package com.anolesoft.amljmes.fortysix.service.mapper;

import com.anolesoft.amljmes.fortysix.dto.BaoZhangZhuangEmpDTO;
import com.anolesoft.amljmes.fortysix.dto.MesBzzBindDto;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

@Mapper
public interface MesBzzBindServiceMapper {
    // 根据配套单明细主键查询
    @Select("select t.objuid,\n" +
            "       t.ptdmx_obj,\n" +
            "       t.ptd_obj,\n" +
            "       t.paigonghao,\n" +
            "       t.jixing,\n" +
            "       t.tuhao,\n" +
            "       t.jiaofuchejian,\n" +
            "       t.jieshouchejian,\n" +
            "       t.jiaofushunxu,\n" +
            "       t.hegebiaoqian,\n" +
            "       t.bangdingnum,\n" +
            "       a.name              bangdingren,\n" +
            "       t.bangdingtime,\n" +
            "       t.xianghao,\n" +
            "       t.dingdanbianhao,\n" +
            "       b.name              tijiaojianyanren,\n" +
            "       t.tijiaojianyantime,\n" +
            "       t.jianyanstate,\n" +
            "       c.name              jianyanren,\n" +
            "       t.jianyantime,\n" +
            "       t.yijiaoren,\n" +
            "       t.yijiaotime,\n" +
            "       t.peitaodanhao,\n" +
            "       t.yijiaodanhao,\n" +
            "       t.bujiandanhao,\n" +
            "       t.kucun_obj,\n" +
            "       t.state,\n" +
            "       t.factory_id,\n" +
            "       t.danwei,\n" +
            "       t.danjishu,\n" +
            "       t.pici,\n" +
            "       t.jiaci,\n" +
            "       d.name              shenpiren,\n" +
            "       t.shenpitime,\n" +
            "       e.name              tishenren,\n" +
            "       t.tishentime,\n" +
            "       f.name              chehiuren,\n" +
            "       t.chehiutime\n" +
            "  from plan_goods_bind t\n" +
            "  left join stf_employee a\n" +
            "    on t.bangdingren = a.objuid\n" +
            "  left join stf_employee b\n" +
            "    on t.tijiaojianyanren = b.objuid\n" +
            "  left join stf_employee c\n" +
            "    on t.jianyanren = c.objuid\n" +
            "  left join stf_employee d\n" +
            "    on t.shenpiren = d.objuid\n" +
            "  left join stf_employee e\n" +
            "    on t.tishenren = e.objuid\n" +
            "  left join stf_employee f\n" +
            "    on t.chehiuren = f.objuid\n" +
            " where ptdmx_obj = #{ptdmxObj, jdbcType = VARCHAR}\n" +
            "   and state = 0\n")
    List<MesBzzBindDto> queryMesBzzBind(String ptdmxObj);
    // 根据配套单主键查询
    @Select("select t.objuid,\n" +
            "       t.ptdmx_obj,\n" +
            "       t.ptd_obj,\n" +
            "       t.paigonghao,\n" +
            "       t.jixing,\n" +
            "       t.tuhao,\n" +
            "       t.jiaofuchejian,\n" +
            "       t.jieshouchejian,\n" +
            "       t.jiaofushunxu,\n" +
            "       t.hegebiaoqian,\n" +
            "       t.bangdingnum,\n" +
            "       a.name              bangdingren,\n" +
            "       t.bangdingtime,\n" +
            "       t.xianghao,\n" +
            "       t.dingdanbianhao,\n" +
            "       b.name              tijiaojianyanren,\n" +
            "       t.tijiaojianyantime,\n" +
            "       t.jianyanstate,\n" +
            "       c.name              jianyanren,\n" +
            "       t.jianyantime,\n" +
            "       t.yijiaoren,\n" +
            "       t.yijiaotime,\n" +
            "       t.peitaodanhao,\n" +
            "       t.yijiaodanhao,\n" +
            "       t.bujiandanhao,\n" +
            "       t.kucun_obj,\n" +
            "       t.state,\n" +
            "       t.factory_id,\n" +
            "       t.danwei,\n" +
            "       t.danjishu,\n" +
            "       t.pici,\n" +
            "       t.jiaci,\n" +
            "       d.name              shenpiren,\n" +
            "       t.shenpitime,\n" +
            "       e.name              tishenren,\n" +
            "       t.tishentime,\n" +
            "       f.name              chehiuren,\n" +
            "       t.chehiutime\n" +
            "  from plan_goods_bind t\n" +
            "  left join stf_employee a\n" +
            "    on t.bangdingren = a.objuid\n" +
            "  left join stf_employee b\n" +
            "    on t.tijiaojianyanren = b.objuid\n" +
            "  left join stf_employee c\n" +
            "    on t.jianyanren = c.objuid\n" +
            "  left join stf_employee d\n" +
            "    on t.shenpiren = d.objuid\n" +
            "  left join stf_employee e\n" +
            "    on t.tishenren = e.objuid\n" +
            "  left join stf_employee f\n" +
            "    on t.chehiuren = f.objuid\n" +
            " where t.ptd_obj = #{ptdObj, jdbcType = VARCHAR}\n" +
            "   and t.state = 0\n" +
            "   and t.jianyanstate != 0\n")
    List<MesBzzBindDto> queryMesBzzBinds(String ptdObj);

    // 连表查
//    @Select("select a.bangdingnum,b.state from plan_goods_bind a,mes_bzz_ptd b where b.objuid=a.ptd_obj and a.ptd_obj = #{ptdObj, jdbcType=VARCHAR}")
    @SelectProvider(type = MesBzzBindServiceMapper.createSQL.class, method = "queryStateNum")
    List<MesBzzBindDto> queryStateNum(String ptdObj);
    public static class createSQL {
        public static String queryStateNum(@Param("ptdObj") String ptdObj) {
            return new SQL() {
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" a.bangdingnum,b.state from plan_goods_bind a,mes_bzz_ptd b " +
                            "where b.objuid=a.ptd_obj and a.ptd_obj in (");
                    sql.append(ptdObj);
                    sql.append(")");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }
    // 配套单状态
    @SelectProvider(type = MesBzzBindServiceMapper.createSQL1.class, method = "queryState")
    List<MesBzzBindDto> queryState(String ptdObj);
    public static class createSQL1 {
        public static String queryState(@Param("ptdObj") String ptdObj) {
            return new SQL() {
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" a.jianyanstate,b.state from plan_goods_bind a,mes_bzz_ptd b " +
                            "where b.objuid=a.ptd_obj and a.ptd_obj in (");
                    sql.append(ptdObj);
                    sql.append(")");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 检验状态
    @SelectProvider(type = MesBzzBindServiceMapper.createSQL2.class, method = "queryStateJy")
    int queryStateJy(String objuid);
    public static class createSQL2 {
        public static String queryStateJy(@Param("objuid") String objuid) {
            return new SQL() {
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" count(*) from plan_goods_bind a where a.jianyanstate!=2 and a.objuid in (");
                    sql.append(objuid);
                    sql.append(")");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }
    @SelectProvider(type = MesBzzBindServiceMapper.createSQL3.class, method = "queryStateXh")
    int queryStateXh(String objuid);
    public static class createSQL3 {
        public static String queryStateXh(@Param("objuid") String objuid) {
            return new SQL() {
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" count(*) from plan_goods_bind a where a.jianyanstate!=1 and a.objuid in (");
                    sql.append(objuid);
                    sql.append(")");
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

    // 添加
    @Insert("insert into plan_goods_bind" +
            "(ptdmx_obj, ptd_obj, paigonghao," +
            " jixing, tuhao,jiaofuchejian," +
            " jieshouchejian, jiaofushunxu, hegebiaoqian," +
            " bangdingnum, bangdingren, bangdingtime," +
            " xianghao, dingdanbianhao, tijiaojianyanren," +
            " tijiaojianyantime, jianyanren," +
            " jianyantime, yijiaoren, yijiaotime," +
            " peitaodanhao, yijiaodanhao, bujiandanhao," +
            " kucun_obj, factory_id, danwei," +
            " danjishu, pici, jiaci," +
            " shenpiren, shenpitime, tishenren," +
            " tishentime, chehiuren, chehiutime)" +
            "values" +
            "(#{ptdmxObj, jdbcType=VARCHAR},#{ptdObj, jdbcType=VARCHAR},#{paigonghao, jdbcType=VARCHAR}," +
            "#{jixing, jdbcType=VARCHAR},#{tuhao, jdbcType=VARCHAR},#{jiaofuchejian, jdbcType=VARCHAR}," +
            "#{jieshouchejian, jdbcType=VARCHAR},#{jiaofushunxu, jdbcType=VARCHAR},#{hegebiaoqian, jdbcType=VARCHAR}," +
            "#{bangdingnum, jdbcType=NUMERIC},#{bangdingren, jdbcType=VARCHAR}, sysdate," +
            "#{xianghao, jdbcType=VARCHAR},#{dingdanbianhao, jdbcType=VARCHAR},#{tijiaojianyanren, jdbcType=VARCHAR}," +
            "to_date(#{tijiaojianyantime, jdbcType=DATE},'yyyy-mm-dd'),#{jianyanren, jdbcType=VARCHAR}," +
            "to_date(#{jianyantime, jdbcType=DATE},'yyyy-mm-dd'),#{yijiaoren, jdbcType=VARCHAR},to_date(#{yijiaotime, jdbcType=DATE},'yyyy-mm-dd')," +
            "#{peitaodanhao, jdbcType=VARCHAR},#{yijiaodanhao, jdbcType=VARCHAR},#{bujiandanhao, jdbcType=VARCHAR}," +
            "#{kucunObj, jdbcType=VARCHAR},#{factoryId, jdbcType=VARCHAR},#{danwei, jdbcType=VARCHAR}," +
            "#{danjishu, jdbcType=VARCHAR}, #{pici, jdbcType=VARCHAR}, #{jiaci, jdbcType=VARCHAR}," +
            "#{shenpiren, jdbcType=VARCHAR}, to_date(#{shenpitime, jdbcType=DATE},'yyyy-mm-dd'), #{tishenren, jdbcType=VARCHAR}," +
            "to_date(#{tishentime, jdbcType=DATE},'yyyy-mm-dd'),#{chehiuren, jdbcType=VARCHAR}, to_date(#{chehiutime, jdbcType=DATE},'yyyy-mm-dd'))")
    int addMesBzzBind(MesBzzBindDto inFoDtoParam);

    // 删除
    @Delete("delete from plan_goods_bind where objuid = #{objuid, jdbcType=VARCHAR} ")
    int deleteMesBzzBind(String objuid);

    // 修改
    // 绑定数量减少
    @Update("update plan_goods_bind set bangdingnum = bangdingnum - #{num1, jdbcType = NUMERIC} where objuid = #{objuid, jdbcType=VARCHAR}" )
    int updateMesBzzBind(@Param("objuid")String objuid,@Param("num1") int num1);
    // 状态为已检验
    @Update("update plan_goods_bind set jianyanstate = 0,jianyanren = #{jianyanren, jdbcType=VARCHAR},jianyantime = sysdate where objuid in (objuid)" )
    int updateBindJystate(@Param("objuid")String objuid,@Param("jianyanren")String jianyanren);
    // 检验条码
    @Select("select t.objuid, t.eid, t.name, t.password, t.password2, t.factory_id\n" +
            "  from stf_employee t\n" +
            " where t.delete_flag = 0\n" +
            "   and t.now_quarters like '%检验%'\n" +
            "   and t.factory_id = #{cdh, jdbcType=VARCHAR}\n" +
            "   and t.eid != 'admin'")
    List<BaoZhangZhuangEmpDTO> queryEmpEid(@Param("cdh")String cdh);

    // 生成箱号
    @Update("update plan_goods_bind set xianghao = 'XH'||to_char(sysdate,'yyyy-mm-dd')||lpad(seq_xh_code.nextval,4,0) where objuid = #{objuid, jdbcType=VARCHAR}" )
    int updateBindXh(String objuid);
}
